package com.itheima.dao;

import com.github.pagehelper.Page;
import com.itheima.pojo.Member;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface MemberDao {
    public List<Member> findAll();
    public Page<Member> selectByCondition(String queryString);
    public void add(Member member);
    public void deleteById(Integer id);
    public Member findById(Integer id);
    public Member findByTelephone(String telephone);
    public void edit(Member member);
    public Integer findMemberCountBeforeDate(String date);
    public Integer findMemberCountByDate(String date);
    public Integer findMemberCountAfterDate(String date);
    public Integer findMemberTotalCount();

    /**
     * 用于查询会员数量性别占比
     * @return
     */
    @Select("SELECT CASE tm.sex  WHEN '1' THEN '男性会员' WHEN '2' THEN '女性会员' END AS name ,count(id) value FROM t_member tm WHERE sex!='0' and sex!='null'GROUP BY sex")
    List<Map> getSexReport();

    /**
     * 年龄段占比统计
     */
    @Select("  SELECT (CASE    WHEN a.age IS NULL THEN 'unknown' WHEN a.age <=18 THEN '0-18岁'  WHEN a.age BETWEEN 18 AND 30 THEN '18-30岁'    WHEN a.age BETWEEN 31 AND 45 THEN '30-45岁'    WHEN a.age > 45 THEN '45岁以上'    END) as name,    COUNT(*) value   FROM (SELECT * FROM (" +
            "SELECT DATE_FORMAT( substring( t.idCard, 7, 8 ), '%Y-%m-%d' ) '出生日期', YEAR ( now( ) ) - YEAR ( substring( t.idCard, 7, 8 ) ) 'age' FROM t_member t WHERE t.idCard != 'nill'  ) temp)a    GROUP BY name")
    List<Map> getAgeReport();

}
